package com.web.hotel.dao;

import com.util.LogUtil;
import com.util.SqlUtil;
import com.web.hotel.po.Room;
import com.web.hotel.service.RoomService;
import com.web.hotel.vo.RoomVo;
import com.web.hotel.vo.mapper.RoomVoMapper;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service(value="roomService")
@Transactional(rollbackFor = { Exception.class })
public class RoomDAO implements RoomService{

    @Resource
    SessionFactory factory;
@Autowired
private JdbcTemplate jdbcTemplate;

    public int saveOrUpdateRoom(Room room) {
        try{
            factory.getCurrentSession().saveOrUpdate(room);
            return 1;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }


    public int deleteRoom(Room room) {
        try{
            factory.getCurrentSession().delete(room);
            return 1;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }


    @Transactional(propagation= Propagation.NOT_SUPPORTED)
    public Map<String, Object> listGrid(HttpServletRequest request) {
        List<Object> params = new ArrayList<Object>();
        List<RoomVo> roomList = new ArrayList<RoomVo>();
        Map<String, Object> dataMap = new HashMap<String, Object>();
        Session s = null;
        /**
         * 接受客户端传递的排序参数
         *
         */
        String sortOrder = request.getParameter("order");
        String sortField = request.getParameter("sort");

        /**
         * 接受客户端传递的分页参数
         *
         */
        String currentPage = request.getParameter("page");
        String pageSize = request.getParameter("rows");
        if(currentPage == null || currentPage.equals("")){
            currentPage = "1";
        }
        if(pageSize == null || pageSize.equals("")){
            pageSize = "10";
        }
        int int_currentPage = Integer.parseInt(currentPage);
        int int_pageSize = Integer.parseInt(pageSize);

        int startRow = (int_currentPage - 1)*int_pageSize;
        int rsCount = 0 ;
        String sql =
                "SELECT\n" +
                        "	r.roomId,\n" +
                        "	r.roomName,\n" +
                        "	r.roomType,\n" +
                        "	r.roomStatus,\n" +
                        "	r.roomPay,\n" +
                        "	r.roomDescription,\n" +
                        "	r.roomPicture,\n" +
                        "	h.hotelName\n" +
                        "FROM\n" +
                        "	t_room r\n" +
                        "LEFT JOIN t_hotel h ON r.hotelId = h.hotelId";

        /**
         * 接收客户端的查询参数
         *
         */
        String param_roomName = request.getParameter("param_roomName");
        String param_roomType = request.getParameter("param_roomType");
        String param_hotelId = request.getParameter("param_hotelId");
        String param_roomPayMin = request.getParameter("param_roomPayMin");
        String param_roomPayMax = request.getParameter("param_roomPayMax");
        String param_roomDesc = request.getParameter("param_roomDesc");
        String param_roomStatus = request.getParameter("param_roomStatus");
        StringBuffer querySQL = new StringBuffer();
        StringBuffer countSQL = new StringBuffer();
        StringBuffer whereSQL = new StringBuffer();

        if(param_roomName != null && !param_roomName.equals("")){
            sql+=" and roomName like ? ";
            params.add("%"+param_roomName+"%");
        }
        if(param_roomType != null && !param_roomType.equals("")){
            sql+=" and roomType like ? ";
            params.add("%"+param_roomType+"%");
        }
        if(param_hotelId != null && !param_hotelId.equals("")){
            sql+=" and hotelId like ? ";
            params.add("%"+param_hotelId+"%");
        }
        if(param_roomPayMin != null && !param_roomPayMin.equals("")){
            sql+=" and roomPay >= ? ";
            params.add(param_roomPayMin);
        }
        if(param_roomPayMax != null && !param_roomPayMax.equals("")){
            sql+=" and roomPay <= ? ";
            params.add(param_roomPayMax);
        }
        if(param_roomDesc != null && !param_roomDesc.equals("")){
            sql+=" and roomDesc like ? ";
            params.add("%"+param_roomDesc+"%");
        }
        if(param_roomStatus != null && !param_roomStatus.equals("")){
            sql+=" and roomStatus = ? ";
            params.add(param_roomStatus);
        }

        /**
         * 1.先取出总记录数
         */
        countSQL.append(SqlUtil.getCountSql(sql));
        LogUtil.sqlLog("房间总记录数",countSQL.toString());

        if(StringUtils.isNotEmpty(sortField)){
            sql+=" order by "+sortField+" "+sortOrder+"";
        }else{
            sql+=" order by roomId asc";
        }
        /**
         * 2.产生分页语句
         */
        querySQL.append(SqlUtil.getMysqlPageSql(sql,Integer.parseInt(currentPage),Integer.parseInt(pageSize)));
        LogUtil.sqlLog("房间分页语句",querySQL.toString());
        try {
            rsCount = jdbcTemplate.queryForObject(countSQL.toString(),Integer.class,params.toArray());
            roomList = jdbcTemplate.query(querySQL.toString(),params.toArray(),new RoomVoMapper());
        } catch (Exception e) {
            e.printStackTrace();
        }
        dataMap.put("total",rsCount);
        dataMap.put("rows",roomList);

        return dataMap;
    }

    public Map<String, Object> deleteRoomById(String roomIds) {
        Map<String, Object> dataMap = new HashMap<String, Object>();
        Session s = null;
        StringBuffer deleteSQL = new StringBuffer();
        deleteSQL.append("delete ");
        deleteSQL.append(" from t_room where roomId in");
        deleteSQL.append("("+roomIds+")");

        try {
            s = factory.getCurrentSession();
            int count = s.createSQLQuery(deleteSQL.toString()).executeUpdate();
            dataMap.put("flag", true);
            dataMap.put("msg", "成功删除"+count+"条数据");
        } catch (Exception e) {
            e.printStackTrace();
            dataMap.put("flag", false);
            dataMap.put("msg", e.getMessage());
        }
        return dataMap;
    }

    public Map<String, Object> addRoom(Room room) {
        Map<String, Object> dataMap = new HashMap<String, Object>();
        if(room == null ){
            dataMap.put("flag", false);
            dataMap.put("msg", "服务端未接收到客户端传递的数据");
            return dataMap;
        }
        try{
            factory.getCurrentSession().save(room);
            dataMap.put("flag", true);
            dataMap.put("msg", "保存房间成功,继续添加？");
        }catch(Exception e){
            e.printStackTrace();
            dataMap.put("flag", false);
            dataMap.put("msg", e.getMessage());
        }

        return dataMap;
    }

    @Transactional(propagation=Propagation.NOT_SUPPORTED)
    public Room getRoomById(int roomId) {
        return (Room)factory.getCurrentSession().get(Room.class, roomId);
    }


    public Map<String, Object> editRoom(Room room) {
        Map<String, Object> dataMap = new HashMap<String, Object>();
        if(room == null ){
            dataMap.put("flag", false);
            dataMap.put("msg", "服务端未接收到客户端传递的数据");
            return dataMap;
        }
        try{
            factory.getCurrentSession().update(room);
            dataMap.put("flag", true);
            dataMap.put("msg", "修改成功,确认修改？");
        }catch(Exception e){
            e.printStackTrace();
            dataMap.put("flag", false);
            dataMap.put("msg", e.getMessage());
        }
        return dataMap;
    }


    public Room getRoomByRoomName(String roomName) {
        Session s = null;
        StringBuffer queryHQL = new StringBuffer();
        queryHQL.append("from Room");
        queryHQL.append(" where roomName = '"+ roomName+"'");
        try{
            List<Room> roomList = factory.getCurrentSession().createQuery(queryHQL.toString()).list();
            if(roomList != null && roomList.size() > 0){
                return roomList.get(0);
            }else{
                return null;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

}
